﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;
using System.Data;
namespace DAO
{
   public class DatHangDAO
    {
        public int layIDKhachHangMax()
        {
            int kq = 1;
            DataTable dt = new DataTable();
            OleDbConnection cn = DataProvider.ConnectDB();
            string str = "Select MAX(MaKhachHang) from KHACHHANG";
            OleDbDataAdapter da = new OleDbDataAdapter(str, cn);
            da.Fill(dt);
            cn.Close();
            DataRow dr = dt.Rows[0];

            kq = int.Parse(dr[0].ToString());
            return kq;
        }

        public int layIDHoaDonMax()
        {
            int kq = 1;
            DataTable dt = new DataTable();
            OleDbConnection cn = DataProvider.ConnectDB();
            string str = "Select MAX(MaHoaDon) from HOADON";
            OleDbDataAdapter da = new OleDbDataAdapter(str, cn);
            da.Fill(dt);
            cn.Close();
            DataRow dr = dt.Rows[0];

            kq = int.Parse(dr[0].ToString());
            return kq;
        }

       public DataTable layBangMatHang()
        {
            DataTable dt =  new DataTable();

            OleDbConnection cn = DataProvider.ConnectDB();
           
            string str = "Select TenHang as 'Tên hàng', DonGia as 'Đơn giá' from MATHANG "; //MaMatHang,TenHang,MaXuatXu,DVTinh,SoLuongTon,GiaNhap,TinhTrang,GhiChu,GiaBan
            OleDbDataAdapter da = new OleDbDataAdapter(str,cn);
            da.Fill(dt);
            cn.Close();

            return dt;
        }

       public DataTable layTTHoaDon()
       {
           DataTable dt = new DataTable();

           OleDbConnection cn = DataProvider.ConnectDB();

           string str = "Select MaKhachHang as 'Mã Khách Hàng',NgayGiao as 'Ngày Giao',DiaChiGiao as 'Địa Chỉ Giao',TrangThai as 'Trạng Thái',MaHoaDon as 'Mã Hóa Đơn' from HOADON where  NgayGiao >= #" +  DateTime.Today + "#  ORDER BY NgayGiao ASC"; 
           OleDbDataAdapter da = new OleDbDataAdapter(str, cn);
           da.Fill(dt);
           cn.Close();

           return dt;
       }
       public DataTable laySPTheoLoai(int loai)
       {
           DataTable dt = new DataTable();

           OleDbConnection cn = DataProvider.ConnectDB();

           string str = "Select TenHang as 'Tên hàng', DonGia as 'Đơn giá' from MATHANG where IDLoai = " + loai; //MaMatHang,TenHang,MaXuatXu,DVTinh,SoLuongTon,GiaNhap,TinhTrang,GhiChu,GiaBan
           OleDbDataAdapter da = new OleDbDataAdapter(str, cn);
           da.Fill(dt);
           cn.Close();

           return dt;
       }

       public int themHoaDon(HoaDonDTO hdDTO)
       {
           DataProvider dp = new DataProvider();
           OleDbConnection cn = DataProvider.ConnectDB();
           OleDbCommand cmd = new OleDbCommand();
           string str = "insert into HOADOn(NgayLap,MaKhachHang,TenNguoiNhan,DienThoai,DiaChiGiao,NgayGiao,TraTruoc,ConNo,TrangThai)  values(?,?,?,?,?,?,?,?,?)";
           cmd = new OleDbCommand(str, cn);
           OleDbParameter para = cmd.Parameters.Add("@ngaylap", OleDbType.VarChar);
           para.Value = hdDTO.NgayLap;
           para = cmd.Parameters.Add("@makh", OleDbType.VarChar);
           para.Value = hdDTO.MaKhachHang;
           para = cmd.Parameters.Add("@tennn", OleDbType.VarChar);
           para.Value = hdDTO.TenNguoiNhan;

           para = cmd.Parameters.Add("@dtt", OleDbType.VarChar);
           para.Value = hdDTO.DienThoai;

           para = cmd.Parameters.Add("@diachigiao", OleDbType.VarChar);
           para.Value = hdDTO.DiaChiGiao;

           para = cmd.Parameters.Add("@ngaygiao", OleDbType.Date);
           para.Value = hdDTO.NgayGiao;


           para = cmd.Parameters.Add("@tratruoc", OleDbType.Numeric);
           para.Value = hdDTO.TraTruoc;

           para = cmd.Parameters.Add("@conno", OleDbType.Numeric);
           para.Value = hdDTO.ConNo;


           para = cmd.Parameters.Add("@trangthai", OleDbType.Numeric);
           para.Value = 1;

           int kq = cmd.ExecuteNonQuery();
           cn.Close();
           if (kq >= 0)
           {
               return 1;
           }
           else
           {
               return 0;
           }
       }

       public int themKhachHang(KhachHangDTO khDTO)
       {
           DataProvider dp = new DataProvider();
           OleDbConnection cn = DataProvider.ConnectDB();
           OleDbCommand cmd = new OleDbCommand();
           string str = "insert into KHACHHANG(TenKhachHang,SDTKhachHang)  values(?,?)";
           cmd = new OleDbCommand(str, cn);
           OleDbParameter para = cmd.Parameters.Add("@tenkh", OleDbType.VarChar);
           para.Value =  khDTO.TenKhachHang;

           para = cmd.Parameters.Add("@sdtKhachHang", OleDbType.Numeric);
           para.Value = khDTO.SoDTKhachHang;

           int kq = cmd.ExecuteNonQuery();
           cn.Close();
           if (kq >= 0)
           {
               return 1;
           }
           else
           {
               return 0;
           }
       }

       public int themChiTietHoaDon(MatHang_DTO mh, int mahd)
       {
           DataProvider dp = new DataProvider();
           OleDbConnection cn = DataProvider.ConnectDB();
           OleDbCommand cmd = new OleDbCommand();
           string str = "insert into ChiTietHoaDon(TenMatHang,SoLuong,MaHoaDon)  values(?,?,?)";
           cmd = new OleDbCommand(str, cn);
           OleDbParameter para = cmd.Parameters.Add("@tenhang", OleDbType.VarChar);
           para.Value = mh.TenHang;

           para = cmd.Parameters.Add("@soluong", OleDbType.Numeric);
           para.Value = mh.SoLuong;

           para = cmd.Parameters.Add("@mahd", OleDbType.Numeric);
           para.Value = mahd;// mã hóa đơn

           int kq = cmd.ExecuteNonQuery();
           cn.Close();
           if (kq >= 0)
           {
               return 1;
           }
           else
           {
               return 0;
           }
       }
       
    }
}
